import { RowDataPacket } from 'mysql2';
import { query, queryOne, insert, update, remove, beginTransaction, commit, rollback } from "../db";
/**
 * 视频相关的数据库操作
 */
export class VideoService {
  /**
   * 获取所有视频
   */
  static async getAllVideos(limit = 20, offset = 0) {
    // 在此sql基础上新增一列获取总数
    return await query(
      `
        
SELECT    v.id,
          v.title,
          v.cover_url AS coverUrl,
          v.duration_seconds AS durationSeconds,
          c.name as category,
          v.description,
          v.source_url AS sourceUrl,
          v.published_at AS publishedAt,
          v.created_at AS createdAt,
          v.updated_at AS updatedAt,
					t.totalCount
        FROM (
					SELECT COUNT(*) AS totalCount
					FROM videos v
					LEFT JOIN categories c ON c.id = v.category
				) t 
        JOIN (
    SELECT 
        v.id,
        v.title,
        v.cover_url,
        v.duration_seconds,
        v.category,
        v.description,
        v.source_url,
        v.published_at,
        v.created_at,
        v.updated_at
    FROM videos v
    ORDER BY v.created_at DESC
     LIMIT ? OFFSET ?
) v ON 1 = 1
LEFT JOIN categories c ON c.id = v.category
      `,
      [limit, offset]
    );
  }

  /**
   * 根据ID获取视频
   */
  static async getVideoById(id: string) {
    return await query(
      `
        SELECT 
          id,
          title,
          cover_url AS coverUrl,
          duration_seconds AS durationSeconds,
          category,
          description,
          source_url AS sourceUrl,
          published_at AS publishedAt,
          created_at AS createdAt,
          updated_at AS updatedAt
        FROM videos WHERE id = ?
      `,
      [id]
    );
  }

  /**
   * 根据分类获取视频
   */
  static async getVideosByCategory(category: string, limit = 20, offset = 0) {
    return await query(
      `
        SELECT 
          id,
          title,
          cover_url AS coverUrl,
          duration_seconds AS durationSeconds,
          category,
          description,
          source_url AS sourceUrl,
          published_at AS publishedAt,
          created_at AS createdAt,
          updated_at AS updatedAt
        FROM videos 
        WHERE category = ? 
        ORDER BY created_at DESC 
        LIMIT ? OFFSET ?
      `,
      [category, limit, offset]
    );
  }

  /**
   * 搜索视频
   */
  static async searchVideos(keyword: string, limit = 20, offset = 0) {
    return await query(
      `
        SELECT 
          id,
          title,
          cover_url AS coverUrl,
          duration_seconds AS durationSeconds,
          category,
          description,
          source_url AS sourceUrl,
          published_at AS publishedAt,
          created_at AS createdAt,
          updated_at AS updatedAt
        FROM videos 
        WHERE title LIKE ? OR description LIKE ?
        ORDER BY created_at DESC 
        LIMIT ? OFFSET ?
      `,
      [`%${keyword}%`, `%${keyword}%`, limit, offset]
    );
  }

  /**
   * 添加视频
   */
  static async addVideo(videoData: {
    id: string;
    title: string;
    coverUrl?: string;
    durationSeconds?: number;
    category: string;
    description?: string;
    sourceUrl?: string;
    publishedAt?: string;
  }) {
    return await insert(
      `
        INSERT INTO videos (id, title, cover_url, duration_seconds, category, description, source_url)
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `,
      [
        videoData.id,
        videoData.title,
        videoData.coverUrl,
        videoData.durationSeconds || 0,
        videoData.category,
        videoData.description,
        videoData.sourceUrl,
      ]
    );
  }

  /**
   * 更新视频
   */
  static async updateVideo(
    id: string,
    updateData: Partial<{
      title: string;
      coverUrl: string;
      durationSeconds: number;
      category: string;
      description: string;
      sourceUrl: string;
      publishedAt: string;
    }>
  ) {
    const fields: string[] = [];
    const values: unknown[] = [];

    Object.entries(updateData).forEach(([key, value]) => {
      if (value !== undefined) {
        // 将key转换为数据库字段名
        const dbKey = key.replace(/([A-Z])/g, (_, p1) => `_${p1.toLowerCase()}`);
        fields.push(`${dbKey} = ?`);
        values.push(value);
      }
    });

    if (fields.length === 0) {
      throw new Error("没有要更新的字段");
    }

    values.push(id);
    return await update(
      `
        UPDATE videos SET ${fields.join(", ")} WHERE id = ?
      `,
      values
    );
  }

  /**
   * 删除视频
   */
  static async deleteVideo(id: string) {
    return await remove(
      `DELETE FROM videos WHERE id = ?`,
      [id]
    );
  }

  /**
   * 添加或更新评分
   */
  static async rateVideo(userid: string, videoId: string, rating: number) {
    const connection = await beginTransaction();
    try {
      // 检查评分是否在有效范围内
      if (rating < 1 || rating > 5) {
        throw new Error('评分必须在1-5之间');
      }

      // 检查视频是否存在
      const video = await query(
        `SELECT id FROM videos WHERE id = ?`,
        [videoId]
      );
      
      if (video.length === 0) {
        throw new Error('视频不存在');
      }

      // 使用INSERT ON DUPLICATE KEY UPDATE来添加或更新评分
      await connection.execute(
        `
          INSERT INTO ratings (user_id, video_id, rating)
          VALUES (?, ?, ?)
          ON DUPLICATE KEY UPDATE rating = VALUES(rating)
        `,
        [userid, videoId, rating]
      );

      // 重新计算视频的平均评分
      const [results] = await connection.execute(
        `
          SELECT 
            AVG(rating) as avg_rating,
            COUNT(*) as rating_count
          FROM ratings
          WHERE video_id = ?
        `,
        [videoId]
      );
      let stats: { avg_rating: number; rating_count: number } | undefined = undefined;
      if (Array.isArray(results) && results.length > 0 && 'id' in results[0]) {
        stats = results[0] as { avg_rating: number; rating_count: number };
      }

      // 更新视频表中的平均评分和评分人数
      await connection.execute(
        `
          UPDATE videos
          SET 
            average_rating = ?, 
            rating_count = ?
          WHERE id = ?
        `,
        [stats?.avg_rating, stats?.rating_count, videoId]
      );

      await commit(connection);
      return { success: true, averageRating: stats?.avg_rating, ratingCount: stats?.rating_count };
    } catch (error) {
      await rollback(connection);
      throw error;
    }
  }

  /**
   * 获取用户对视频的评分
   */
  static async getUserRating(userId: string, videoId: string) {
    try {
      const result = await queryOne<{ rating: number }>(
        `
          SELECT rating 
          FROM ratings 
          WHERE user_id = ? AND video_id = ?
        `,
        [userId, videoId]
      );
      return result?.rating || null;
    } catch (error) {
      console.error('获取用户评分失败:', error);
      throw error;
    }
  }
  
  /**
   * 添加或更新收藏
   */
  static async toggleFavorite(userId: string, videoId: string, isFavorite: boolean): Promise<boolean> {
    try {
      const connection = await beginTransaction();
      
      try {
        // 检查视频是否存在
        const videoCheck = await query(
          'SELECT 1 FROM videos WHERE id = ?',
          [videoId]
        );
        
        if (videoCheck.length === 0) {
          throw new Error('视频不存在');
        }
        
        // 检查收藏是否已存在
        const favoriteCheck = await query(
          'SELECT 1 FROM favorites WHERE user_id = ? AND video_id = ?',
          [userId, videoId]
        );
        
        if (isFavorite) {
          // 添加收藏
          if (favoriteCheck.length === 0) {
            await insert(
              'INSERT INTO favorites (user_id, video_id, created_at) VALUES (?, ?, NOW())',
              [userId, videoId]
            );
          }
        } else {
          // 取消收藏
          if (favoriteCheck.length > 0) {
            await remove(
              'DELETE FROM favorites WHERE user_id = ? AND video_id = ?',
              [userId, videoId]
            );
          }
        }
        
        await commit(connection);
        return true;
      } catch (error) {
        await rollback(connection);
        throw error;
      }
    } catch (error) {
      console.error('切换收藏状态失败:', error);
      throw error;
    }
  }

  /**
   * 检查视频是否已被用户收藏
   */
  static async isVideoFavorited(userId: string, videoId: string): Promise<boolean> {
    try {
      const result = await query(
        'SELECT 1 FROM favorites WHERE user_id = ? AND video_id = ?',
        [userId, videoId]
      );
      return result.length > 0;
    } catch (error) {
      console.error('检查收藏状态失败:', error);
      throw error;
    }
  }

  /**
   * 获取用户收藏的视频列表
   */
  static async getUserFavorites(userId: string, limit: number = 20, offset: number = 0) {
    try {
      const sql = `
        SELECT v.id,
               v.title,
               v.cover_url AS coverUrl,
               v.duration_seconds AS durationSeconds,
               c.name as category,
               v.description,
               v.source_url AS sourceUrl,
               v.published_at AS publishedAt,
               v.created_at AS createdAt,
               v.updated_at AS updatedAt,
               v.average_rating AS averageRating,
               v.rating_count AS ratingCount,
               r.rating as userRating,
               1 as isFavorite
        FROM favorites f
        JOIN videos v ON f.video_id = v.id
        LEFT JOIN categories c ON c.id = v.category
        LEFT JOIN ratings r ON v.id = r.video_id AND r.user_id = ?
        WHERE f.user_id = ?
        ORDER BY f.created_at DESC
        LIMIT ? OFFSET ?
      `;
      
      return await query(sql, [userId, userId, limit, offset]);
    } catch (error) {
      console.error('获取用户收藏列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取带有用户评分和收藏状态的视频详情
   */
  static async getVideoWithUserInfo(id: string, userId?: string) {
    try {
      const videoResult = await this.getVideoById(id);
      
      if (videoResult.length === 0) {
        return null;
      }

      const video = videoResult[0];
      
      // 如果提供了用户ID，获取用户评分和收藏状态
      if (userId) {
        const userRating = await this.getUserRating(userId, id);
        const isFavorite = await this.isVideoFavorited(userId, id);
        return {
          ...video,
          userRating,
          isFavorite
        };
      }

      return video;
    } catch (error) {
      console.error('获取视频详情(含用户信息)失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有视频（包含平均评分）
   */
  static async getAllVideosWithRatings(limit = 20, offset = 0, userId?: number) {
    // 基础查询
    let baseQuery = `
      SELECT 
        v.id,
        v.title,
        v.cover_url AS coverUrl,
        v.duration_seconds AS durationSeconds,
        c.name as category,
        v.description,
        v.source_url AS sourceUrl,
        v.published_at AS publishedAt,
        v.created_at AS createdAt,
        v.updated_at AS updatedAt,
        v.average_rating AS averageRating,
        v.rating_count AS ratingCount
      FROM videos v
      LEFT JOIN categories c ON c.id = v.category
      ORDER BY v.created_at DESC
      LIMIT ? OFFSET ?
    `;

    const videos = await query(baseQuery, [limit, offset]);

    // 如果提供了用户ID，获取每个视频的用户评分和收藏状态
    if (userId) {
      const videoIds = videos.map((v: any) => v.id);
      
      if (videoIds.length > 0) {
        // 批量获取用户评分
        const userRatings = await query(
          `
            SELECT video_id, rating 
            FROM ratings 
            WHERE user_id = ? AND video_id IN (${videoIds.map(() => '?').join(',')})
          `,
          [userId, ...videoIds]
        );

        // 批量获取收藏状态
        const userFavorites = await query(
          `
            SELECT video_id 
            FROM favorites 
            WHERE user_id = ? AND video_id IN (${videoIds.map(() => '?').join(',')})
          `,
          [userId, ...videoIds]
        );

        // 创建映射表以便快速查找
        const ratingMap = new Map(userRatings.map((r: any) => [r.video_id, r.rating]));
        const favoriteMap = new Set(userFavorites.map((f: any) => f.video_id));

        // 为每个视频添加用户特定信息
        return videos.map((video: any) => ({
          ...video,
          userRating: ratingMap.get(video.id) || null,
          isFavorite: favoriteMap.has(video.id)
        }));
      }
    }

    return videos;
  }
}
